Oversized Job Report

What is an Oversized Job Report?

The Oversized Job report provides a view of the Collibra DQ jobs that have more hardware assigned than is available to run. For example, say you have a scenario where 20 jobs all request 100 servers to kick off at the same time, but 2,000 servers are not available to run. The Oversized Job report identifies those 20 jobs, which are oversized. Those jobs can now be resized to the optimal amount of executors, cores, ram, etc.

Steps

To generate an Oversized Job report, follow these steps.

  1. Sign in to the Collibra DQ instance and click the reports icon Reports icon in the left navigation pane.
    The Reports page opens.

  2. From the Reports page, click the Oversized Job Report link.

  3. To configure the type of chart configuration and data you want to see in the chart, click the ellipsis icon.

  4. Click Chart Configuration.
    The Chart Configuration window displays.

    Note As a best practice, keep the Chart Configuration as the default.

  5. For x-axis, select one of the following options from the X-Axis Column dropdown list:
    • dataset
    • reason
    • recommended_total_memory
    • recommended_total_cores
    • recommended_num_executors
    • recommended_executor_memory
    • num_executors
    • executor_cores
    • executor_memory
    • total_cores
    • total_memory
    • avg_row_count
    • column_count
    • avg_total_time_in_minutes
    • cell_count

  6. For y-axis, select one or more of the following options from the Y-Axis Columns dropdown list:

    • dataset
    • reason
    • recommended_total_memory
    • recommended_total_cores
    • recommended_num_executors
    • recommended_executor_memory
    • num_executors
    • executor_cores
    • executor_memory
    • total_cores
    • total_memory
    • avg_row_count
    • column_count
    • avg_total_time_in_minutes
    • cell_count

  7. For the type of chart you want to display, select one of the following options from the Chart Type dropdown list:

    • Line
    • Area
    • Column
    • Scatter

  8. Click Save.

  9. Navigate the pages of your report by clicking the Previous and Next pagination buttons, located bottom-right of the columns.

Sample SQL query

You can use the following sample SQL queries for an Oversized Job report.

Copy 
DROP VIEW IF EXISTS report_oversized_jobs;
CREATE OR REPLACE VIEW report_oversized_jobs AS
(
WITH most_current_dataset_scan AS (
    SELECT dataset_scan.dataset,
           avg(dataset_scan.rc)::integer AS avg_row_count
    FROM dataset_scan
    GROUP BY dataset_scan.dataset
),
     dataset_schema_col AS (
         SELECT dataset_schema.dataset,
                count(*) AS column_count
         FROM dataset_schema
         GROUP BY dataset_schema.dataset
     ),
     dataset_activity_time AS (
         SELECT dataset_activity.dataset,
                round(avg(dataset_activity.total_time_in_minutes), 2) AS avg_total_time_in_minutes
         FROM dataset_activity
         GROUP BY dataset_activity.dataset
     ),
     highest_hardware_usage AS (
         SELECT opt.dataset,
                opt.num_executors,
                opt.executor_cores,
                opt.executor_memory,
                opt.executor_cores * opt.num_executors AS total_cores,
                NULLIF(regexp_replace(opt.executor_memory::text, '\D'::text, ''::text, 'g'::text), ''::text)::numeric * opt.num_executors::numeric AS total_memory,
                ds.avg_row_count,
                sch.column_count,
                dat.avg_total_time_in_minutes,
                ds.avg_row_count * sch.column_count AS cell_count
         FROM opt_spark opt,
              most_current_dataset_scan ds,
              dataset_schema_col sch,
              dataset_activity_time dat
         WHERE opt.dataset::text = ds.dataset::text AND sch.dataset::text = ds.dataset::text AND dat.dataset::text = ds.dataset::text AND opt.executor_memory IS NOT NULL AND opt.executor_memory::text NOT LIKE ''::text
         ORDER BY (opt.executor_cores * opt.num_executors) DESC
     )

SELECT
    highest_hardware_usage.dataset AS dataset,
    CASE
        WHEN (highest_hardware_usage.cell_count::numeric / highest_hardware_usage.total_memory) > 20000000::numeric THEN 'Too Much Memory'::text
        WHEN (highest_hardware_usage.cell_count / highest_hardware_usage.total_cores) > 190000000 THEN 'Too Many Cores'::text
        ELSE 'Too Much Memory & Cores'::text
        END AS reason,
    GREATEST(highest_hardware_usage.cell_count / 20000000, 1::decimal) AS recommended_total_memory,
    GREATEST(highest_hardware_usage.cell_count / 119000000, 1::decimal) AS recommended_total_cores,
    GREATEST(highest_hardware_usage.cell_count / 119000000 / 2, 1::decimal) AS recommended_num_executors,
    GREATEST(highest_hardware_usage.cell_count / 20000000, 1::decimal) / GREATEST(highest_hardware_usage.cell_count / 119000000 / 2, 1::decimal) AS recommended_executor_memory,
    highest_hardware_usage.num_executors,
    highest_hardware_usage.executor_cores,
    highest_hardware_usage.executor_memory,
    highest_hardware_usage.total_cores,
    highest_hardware_usage.total_memory,
    highest_hardware_usage.avg_row_count,
    highest_hardware_usage.column_count,
    highest_hardware_usage.avg_total_time_in_minutes,
    highest_hardware_usage.cell_count
FROM highest_hardware_usage
WHERE (
            (highest_hardware_usage.cell_count::numeric / highest_hardware_usage.total_memory) < 20000000::numeric
        OR (highest_hardware_usage.cell_count / highest_hardware_usage.total_cores) < 119000000)
  AND highest_hardware_usage.executor_cores > 1
  AND highest_hardware_usage.num_executors > 1
    );